-- Create database
CREATE DATABASE ExagoReports

-- Create the table that will store reports, folders, themes and document templates
CREATE TABLE Reports
    (
	
		Id NVARCHAR(128)  NOT NULL, --the unique identifier of the item a GUID value
		name VARCHAR(100), -- the name of an item
		type INT, -- differentiates reports/folders from themes and document templates
        leaf_flag BIT, --differentiates reports from folders
		content VARCHAR(MAX), -- the content of reports or themes
		bit_content VARBINARY(MAX), -- the content of document tempaltes
		reportType INT, --differentiates reports by typ e.g. Advanced reports, expressviews, dashboards
		themeType VARCHAR(20), --differentiates themes e.g. charts, maps, expressviews,etc.
		--optional additional properties of each item
        isDeleted BIT, --allows for "soft deletes" instead of deleting the row when a user deletes a report
        dateCreated DATETIME, --shows when the item was created
        createdByUserId VARCHAR(50), --shows who created the item. Relies on the Parameter "userId" being set via the API
        dateLastModified DATETIME, --shows when the item was last modified
        lastModifiedByUserId VARCHAR(50), --shows who last modified the item relies on the Parameter "userId" being set via the API
        ownerId NVARCHAR(128), --specifies who is the "owner" of the report, by default that is the reports creator 
        PRIMARY KEY (Id)
    )
-- Create the table that will store permissions of who can access each report
CREATE TABLE ReportAccess
    (
        partyType INT NOT NULL, --differentiates the type of access, e.g. "everyone", by company, department, individual
        partyId NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, -- the identifier for the party 
        reportId NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, -- Id of the report being granted access to
        parentId NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS, -- Id of the folder the report should reside in for this level of access
		readOnly BIT DEFAULT 0 NOT NULL, -- indicates if the report should be editable
		--optional  properties for each permission row
		isActive BIT DEFAULT 1 NOT NULL, --provides a "soft delete" for access permissions
		sortOrder INT, --allows reports/folders to be sorted in a non alphabetic order
        canRename BIT,--indicates if the report/folder should be renameable/moveable
        PRIMARY KEY (partyType, partyId, reportId)
    )
	
-- Create the table that will store user preferences
	CREATE TABLE UserPreferences
    (
        userId NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, --the id of the user owning the user preferenc. Relies on the Parameter "userId" being set via the API
        upId VARCHAR(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, --the id of the user preference
        upValue VARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS, --the value of the user preference
        PRIMARY KEY (userId, upId),
    )